package com.mzj.saas.commons;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.beans.PropertyDescriptor;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.*;


public class Export2Util {
	public static Workbook getExcel(List<?> datas, Map<String, String> numbs) throws Exception {
		Workbook workbook = new SXSSFWorkbook(1000);
		String sheetName = "sheet1";
		Sheet sheet1 = workbook.createSheet(sheetName);

		// 行对象
		Row nrow = null;
		// 单元格
		Cell ncell = null;


		List<String> list2 = new ArrayList<String> ();
		list2.addAll(numbs.keySet());

		Integer size = list2.size();
		nrow = sheet1.createRow(0);
		if (datas != null && datas.size() > 0) {
			for(Integer i = 0; i < size; i++) {
				// 创建单元格
				ncell = nrow.createCell(i);
				// 设置内容
				ncell.setCellValue(list2.get(i));
			}



			Integer num = 1;
			for (Object object : datas) {
				nrow = sheet1.createRow(num ++);
				// 行高
				nrow.setHeightInPoints(15);

				Class clazz = object.getClass();

				for(Integer i = 0;i < size; i ++) {
					Field field = clazz.getDeclaredField(numbs.get(list2.get(i)));
			        PropertyDescriptor pd=new PropertyDescriptor(field.getName(),clazz);

			        Method readMethod = pd.getReadMethod();
			        String str1 = String.valueOf(readMethod.invoke(object));

			        sheet1.setColumnWidth(i, 15*256);
			        ncell = nrow.createCell(i);
			        if ("null".equals(str1) || str1.isEmpty()) {
			        	//ncell.setCellValue("0");
			        }else {
			        	ncell.setCellValue(str1);
			        }
				}

			}

		}else {
			Row noData = workbook.getSheet(sheetName).createRow(0);
			Cell cell = noData.createCell(0);
			cell.setCellValue("无查询数据");
		}

		return workbook;
	}

	public static Workbook getExcel4(List<Map<String,Object>> datas, Map<String, String> numbs) throws Exception {
		Workbook workbook = new SXSSFWorkbook(1000);
		String sheetName = "sheet1";
		Sheet sheet1 = workbook.createSheet(sheetName);

		// 行对象
		Row nrow = null;
		// 单元格
		Cell ncell = null;


		List<String> list2 = new ArrayList<String> ();
		list2.addAll(numbs.keySet());

		Integer size = list2.size();
		nrow = sheet1.createRow(0);
		if (datas != null && datas.size() > 0) {
			for(Integer i = 0; i < size; i++) {
				// 创建单元格
				ncell = nrow.createCell(i);
				// 设置内容
				ncell.setCellValue(list2.get(i));
			}



			Integer num = 1;
			for (Map<String,Object> object : datas) {
				nrow = sheet1.createRow(num ++);
				// 行高
				nrow.setHeightInPoints(15);

				for(Integer i = 0;i < size; i ++) {
					String field = numbs.get(list2.get(i));
					String str1 = String.valueOf(object.get(field));

					sheet1.setColumnWidth(i, 15*256);
					ncell = nrow.createCell(i);
					if ("null".equals(str1) || str1.isEmpty()) {
						//ncell.setCellValue("0");
					}else {
						ncell.setCellValue(str1);
					}
				}

			}

		}else {
			Row noData = workbook.getSheet(sheetName).createRow(0);
			Cell cell = noData.createCell(0);
			cell.setCellValue("无查询数据");
		}

		return workbook;
	}

	/**
	 *@param datas 导出的数据
	 *@param numbs 字段中文名:字段英文名_默认值_符号 _参数操作_操作值
	 */
	public static Workbook getExcel2(List<?> datas, Map<String, String> numbs) throws Exception {
		Workbook workbook = new SXSSFWorkbook(1000);
		String sheetName = "sheet1";
		Sheet sheet1 = workbook.createSheet(sheetName);

		// 行对象
		Row nrow = null;
		// 单元格
		Cell ncell = null;


		List<String> list2 = new ArrayList<String> ();
		list2.addAll(numbs.keySet());

		Integer size = list2.size();
		nrow = sheet1.createRow(0);
		if (datas != null && datas.size() > 0) {
			for(Integer i = 0; i < size; i++) {
				// 创建单元格
				ncell = nrow.createCell(i);
				// 设置内容
				ncell.setCellValue(list2.get(i));
			}



			Integer num = 1;
			for (Object object : datas) {
				nrow = sheet1.createRow(num ++);
				// 行高
				nrow.setHeightInPoints(15);

				Class clazz = object.getClass();

				for(Integer i = 0;i < size; i ++) {
					String string = numbs.get(list2.get(i));
					String numb1 = numbs.get(list2.get(i)).split("_")[0];//字段名
					String numb2 = numbs.get(list2.get(i)).split("_")[1];//默认值
					String numb3 = numbs.get(list2.get(i)).split("_")[2];//字段符号
					String numb4 = numbs.get(list2.get(i)).split("_")[3];//字段操作
					String numb5 = numbs.get(list2.get(i)).split("_")[4];//操作值



					Field field = clazz.getDeclaredField(numb1);
			        PropertyDescriptor pd=new PropertyDescriptor(field.getName(),clazz);

			        Method readMethod = pd.getReadMethod();
			        String str1 = String.valueOf(readMethod.invoke(object));

			        sheet1.setColumnWidth(i, 15*256);
			        ncell = nrow.createCell(i);
			        if ("null".equals(str1) || str1.isEmpty()) {
			        	//ncell.setCellValue("0");

			        	if(numb2.equals(" ") || numb2 == null) {
			        		ncell.setCellValue("0");
			        	}else {
			        		ncell.setCellValue(numb2);
			        	}
			        }else {

			        	if(numb3.equals(" ") || numb3 == null) {
			        		if(numb4.equals(" ") || numb5.equals(" ")) {
			        			ncell.setCellValue(str1);
			        		}else{

			        			switch(numb4){
			        	        case "/":
			        	        	Double numb = Integer.valueOf(str1)/Double.valueOf(numb5);
			        	        	ncell.setCellValue(numb);
			        	            break;
			        	        default:
			        	        	ncell.setCellValue(str1);
			        	        	break;
			        	        }
			        		}


			        	}else {
			        		ncell.setCellValue(str1 + numb3);
			        	}
			        }
				}

			}

		}else {
			Row noData = workbook.getSheet(sheetName).createRow(0);
			Cell cell = noData.createCell(0);
			cell.setCellValue("无查询数据");
		}

		return workbook;
	}


	/**
	 *@param datas 导出的数据
	 *@param numbs 字段中文名:字段英文名_默认值_符号 _参数操作_操作值
	 */
	public static Workbook getExcel3(List<?> datas, Map<String, String> numbs) throws Exception {
		Workbook workbook = new SXSSFWorkbook(1000);
		String sheetName = "sheet1";
		Sheet sheet1 = workbook.createSheet(sheetName);

		// 行对象
		Row nrow = null;
		// 单元格
		Cell ncell = null;


		List<String> list2 = new ArrayList<String> ();
		list2.addAll(numbs.keySet());

		Integer size = list2.size();
		nrow = sheet1.createRow(0);
		if (datas != null && datas.size() > 0) {
			for(Integer i = 0; i < size; i++) {
				// 创建单元格
				ncell = nrow.createCell(i);
				// 设置内容
				ncell.setCellValue(list2.get(i));
			}



			Integer num = 1;
			for (Object object : datas) {
				nrow = sheet1.createRow(num ++);
				// 行高
				nrow.setHeightInPoints(15);

				Class clazz = object.getClass();

				for(Integer i = 0;i < size; i ++) {
					String string = numbs.get(list2.get(i));
					String numb1 = numbs.get(list2.get(i)).split("_")[0];//字段名
					String numb2 = numbs.get(list2.get(i)).split("_")[1];//默认值
					String numb3 = numbs.get(list2.get(i)).split("_")[2];//字段符号
					String numb4 = numbs.get(list2.get(i)).split("_")[3];//字段操作
					String numb5 = numbs.get(list2.get(i)).split("_")[4];//操作值



					Field field = clazz.getDeclaredField(numb1);
			        PropertyDescriptor pd=new PropertyDescriptor(field.getName(),clazz);

			        Method readMethod = pd.getReadMethod();
			        String str1 = String.valueOf(readMethod.invoke(object));

			        sheet1.setColumnWidth(i, 15*256);
			        ncell = nrow.createCell(i);
			        if ("null".equals(str1) || str1.isEmpty()) {
			        	if(numb2.equals(" ") || numb2 == null) {
			        		ncell.setCellValue("0");
			        	}else {
			        		ncell.setCellValue(numb2);
			        	}
			        }else {

			        	if(numb3.equals(" ") || numb3 == null) {
			        		if(numb4.equals(" ") || numb5.equals(" ")) {
			        			ncell.setCellValue(str1);
			        		}else{

			        			switch(numb4){
			        	        case "/":
			        	        	Double numb = Integer.valueOf(str1)/Double.valueOf(numb5);
			        	        	ncell.setCellValue(numb);
			        	            break;
			        	        default:
			        	        	ncell.setCellValue(str1);
			        	        	break;
			        	        }
			        		}


			        	}else {
			        		ncell.setCellValue(str1 + numb3);
			        	}
			        }

				}

				//每当行数达到设置的值就刷新数据到硬盘,以清理内存
	            if(num%100==0){
	               ((SXSSFSheet)sheet1).flushRows();
	            }
			}


		}else {
			Row noData = workbook.getSheet(sheetName).createRow(0);
			Cell cell = noData.createCell(0);
			cell.setCellValue("无查询数据");
		}

		return workbook;
	}

	/**
	 *加载Excel模板方式
	 *  path :模板路径
	 *  templateName :模板名称
	 *  rowNum : 开始写入行数  [开始行:0]
	 */
	public Workbook getExcelByTemplate(List<?> datas, Map<String, String> numbs,String path,String templateName,Integer rowNum) throws Exception {

		InputStream in = this.getClass().getClassLoader().getResourceAsStream("static/exceltemplates/"+ path +"/"+ templateName);
		XSSFWorkbook wb = new XSSFWorkbook(in);

		SXSSFWorkbook workbook = new SXSSFWorkbook(wb,1000);
		XSSFWorkbook xssfWorkbook = workbook.getXSSFWorkbook();
		Sheet sheet1 = xssfWorkbook.getSheetAt(0);

		// 行对象
		Row nrow = null;
		// 单元格
		Cell ncell = null;
		CellStyle style = workbook.createCellStyle(); // 样式对象
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平

		List<String> list2 = new ArrayList<String> ();
		list2.addAll(numbs.keySet());
		Integer size = list2.size();

		if (datas != null && datas.size() > 0) {
			for (Object object : datas) {
				nrow = sheet1.createRow(rowNum ++);
				// 行高
				nrow.setHeightInPoints(15);

				Class clazz = object.getClass();

				for(Integer i = 0;i < size; i ++) {
					Field field = clazz.getDeclaredField(numbs.get(list2.get(i)));
					PropertyDescriptor pd=new PropertyDescriptor(field.getName(),clazz);

					Method readMethod = pd.getReadMethod();
					String str1 = String.valueOf(readMethod.invoke(object));

					sheet1.setColumnWidth(i, 15*256);
					ncell = nrow.createCell(i);
					if ("null".equals(str1) || str1.isEmpty()) {
						//ncell.setCellValue("0");
					}else {
						ncell.setCellStyle(style);
						ncell.setCellValue(str1);
					}
				}

			}

		}

		return workbook;
	}


	/**
	 * 获取时间段的所有时间点
	 *
	 */
	public static List<Date> formTime(Date beginDate,Date endDate) throws Exception{
	    List<Date> lDate = new ArrayList<>();
	    lDate.add(beginDate);//把开始时间加入集合
	    Calendar cal = Calendar.getInstance();
	    //使用给定的 Date 设置此 Calendar 的时间
	    cal.setTime(beginDate);
	    boolean bContinue = true;
	    while (bContinue) {
	        //根据日历的规则，为给定的日历字段添加或减去指定的时间量
	        cal.add(Calendar.DAY_OF_MONTH, 1);
	        // 测试此日期是否在指定日期之后
	        if (endDate.after(cal.getTime())) {
	            lDate.add(cal.getTime());
	        } else {
	            break;
	        }
	    }
	    if(beginDate.compareTo(endDate) != 0) {
	    	lDate.add(endDate);//把结束时间加入集合
	    }

	    return lDate;

	}

	/**
	 *
	 * 对list集合进行分组
	 */
    public static Map<String, List<String>> groupList(List<String> list,int index){

        int listSize=list.size();
        int toIndex=index;
        Map<String,List<String>> map = new HashMap<>();     //用map存起来新的分组后数据
        int keyToken = 0;
        for(int i = 0;i<list.size();i+=index){
            if(i+index>listSize){        //作用为toIndex最后没有100条数据则剩余几条newList中就装几条
                toIndex=listSize-i;
            }
        List<String> newList = list.subList(i,i+toIndex);
        map.put("keyName"+keyToken, newList);
        keyToken++;
        }

        return map;
    }


}
